package org.zf.database.server.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.lang.PatternPool;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.ReUtil;
import cn.hutool.core.util.StrUtil;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.zf.database.consts.DBPrivsType;
import org.zf.database.consts.DBType;
import org.zf.database.consts.FieldType;
import org.zf.database.domain.*;
import org.zf.database.exception.NotSupportException;
import org.zf.database.server.AbstractCommonDBServer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
import java.util.function.BiFunction;

@Slf4j
public class MySQLDBServerImpl extends AbstractCommonDBServer
{
    public final Map<FieldType, List<String>> TYPEMAP = new HashMap<FieldType, List<String>>()
    {{
        put(FieldType.INT, Arrays.asList("INT,INTEGER,MEDIUMINT,SMALLINT,TINYINT".split(",")));
        put(FieldType.NUMBER, Arrays.asList("BIGINT,FLOAT,DOUBLE,DECIMAL".split(",")));
        put(FieldType.VARCHAR, Arrays.asList("CHAR,VARCHAR,TINYTEXT".split(",")));
        put(FieldType.BOOLEAN, Arrays.asList("BIT".split(",")));
        put(FieldType.DATE, Arrays.asList("DATE,TIME,YEAR,DATETIME".split(",")));
        put(FieldType.TIMESTAMP, Arrays.asList("TIMESTAMP".split(",")));
        put(FieldType.CLOB, Arrays.asList("TEXT,MEDIUMTEXT,LONGTEXT".split(",")));
        put(FieldType.BLOB, Arrays.asList("BLOB,TINYBLOB,MEDIUMBLOB,LONGBLOB".split(",")));
    }};

    @Getter
    private final Map<FieldType, String> DEFAULTMAP = new HashMap<FieldType, String>()
    {{
        put(FieldType.INT, "INT");
        put(FieldType.NUMBER, "DOUBLE");
        put(FieldType.VARCHAR, "VARCHAR({})");
        put(FieldType.BOOLEAN, "BIT");
        put(FieldType.DATE, "DATETIME");
        put(FieldType.TIMESTAMP, "TIMESTAMP");
        put(FieldType.CLOB, "TEXT");
        put(FieldType.BLOB, "BLOB");
    }};

    @Override
    public List<TableSpaceInfo> getTableSpace(DBInfo dbInfo) throws Exception
    {
        List<TableSpaceInfo> list = new ArrayList<>();
        try (Connection conn = getConn(dbInfo);
             Statement sm = conn.createStatement();
             ResultSet rs = sm.executeQuery("SELECT table_schema, (sum(index_length)+sum(data_length)) FROM information_schema.tables GROUP BY table_schema");
        )
        {
            while (rs.next())
            {
                TableSpaceInfo tmp = new TableSpaceInfo();
                tmp.setDbInfo(dbInfo);
                tmp.setSpaceName(rs.getString(1));
                tmp.setTotal(rs.getLong(2));
                tmp.setUsed(tmp.getTotal());
                list.add(tmp);
            }
        }
        return list;
    }

    @Override
    public List<String> user(DBInfo dbInfo) throws Exception
    {
        List<String> user = super.user(dbInfo);
        if (CollUtil.isEmpty(user))
        {
            Set<String> userSet = new HashSet<>();
            try (Connection conn = getConn(dbInfo);
                 Statement sm = conn.createStatement();
                 ResultSet rs = sm.executeQuery("SELECT SCHEMA_NAME FROM information_schema.SCHEMATA ORDER BY SCHEMA_NAME");
            )
            {
                while (rs.next())
                {
                    userSet.add(rs.getString(1));
                }
                return new ArrayList<>(userSet);
            } catch (Exception e)
            {
                log.error("查询异常", e);
                throw e;
            }
        }
        return user;
    }

    @Override
    public List<TableInfo> table(DBInfo dbInfo) throws Exception
    {
        List<TableInfo> table = super.table(dbInfo);
        if (CollUtil.isNotEmpty(table))
            return table;
        try (Connection conn = getConn(dbInfo);
             Statement sm = conn.createStatement();
             ResultSet rs = sm.executeQuery(StrUtil.format("SELECT table_name, table_comment, 1 AS 'isTable', table_rows, data_length, index_length FROM information_schema.tables WHERE table_schema = '{}' UNION SELECT table_name, '' AS table_comment, 0 AS 'isTable', 0 AS table_rows, 0 AS data_length, 0 AS index_length FROM information_schema.views WHERE table_schema = '{}'", dbInfo.getOwner(), dbInfo.getOwner()));
        )
        {
            table = new ArrayList<>();
            while (rs.next())
            {
                table.add(new TableInfo()
                {{
                    setDbInfo(dbInfo);
                    setTableName(rs.getString(1));
                    setComment(rs.getString(2));
                    setIsTable(rs.getBoolean(3));
                    setRowSize(rs.getInt(4));
                    setDataSize(rs.getInt(5));
                    setIndexSize(rs.getInt(6));
                }});
            }
        } catch (Exception e)
        {
            log.error("查询错误", e);
            throw e;
        }
        return table;
    }

    @Override
    public TablePrivsInfo tablePrivs(TableInfo tableInfo) throws Exception
    {
        TablePrivsInfo tablePrivsInfo = new TablePrivsInfo();
        tablePrivsInfo.setTableInfo(tableInfo);
        try (Connection conn = getConn(tableInfo.getDbInfo());
             Statement sm = conn.createStatement();
             ResultSet currentUser = sm.executeQuery("SELECT CURRENT_USER()");
        )
        {
            if (currentUser.next())
            {
                Map<DBPrivsType, Boolean> map = new HashMap<>();
                String[] split = currentUser.getString(1).split("@");
                if ("root".equals(split[0]))
                {
                    tablePrivsInfo.setPrivs(new HashMap<DBPrivsType, Boolean>()
                    {{
                        put(DBPrivsType.SELECT, true);
                        put(DBPrivsType.INSERT, true);
                        put(DBPrivsType.DELETE, true);
                        put(DBPrivsType.UPDATE, true);
                    }});
                    return tablePrivsInfo;
                }

                try (
                        Statement sm2 = conn.createStatement();
                        ResultSet userRs = sm2.executeQuery(StrUtil.format("SELECT SELECT_PRIV, INSERT_PRIV,UPDATE_PRIV, DELETE_PRIV,DROP_PRIV FROM MYSQL.USER WHERE USER = '{}' AND HOST = '{}'", split[0], split[1]));
                        Statement sm3 = conn.createStatement();
                        ResultSet dbRs = sm3.executeQuery(StrUtil.format("SELECT SELECT_PRIV, INSERT_PRIV,UPDATE_PRIV, DELETE_PRIV,DROP_PRIV  FROM MYSQL.DB WHERE USER = '{}' AND HOST = '{}' AND DB = '{}'", split[0], split[1], tableInfo.getDbInfo().getOwner()));
                        Statement sm4 = conn.createStatement();
                        ResultSet tableRs = sm4.executeQuery(StrUtil.format("SELECT TABLE_PRIV FROM MYSQL.TABLES_PRIV WHERE USER = '{}' AND HOST = '{}' AND DB = '{}' AND TABLE_NAME = '{}'", split[0], split[1], tableInfo.getDbInfo().getOwner(), tableInfo.getTableName()));
                )
                {
                    getPrivResult(userRs, map);
                    getPrivResult(dbRs, map);
                    while (tableRs.next())
                    {
                        for (String priv : tableRs.getString(1).split(","))
                        {
                            DBPrivsType dbPrivsType = DBPrivsType.parsePrivs(priv.toUpperCase());
                            map.put(dbPrivsType, true);
                        }
                    }
                }
                tablePrivsInfo.setPrivs(map);
            }
            return tablePrivsInfo;
        } catch (Exception e)
        {
            log.error("查询异常", e);
            throw e;
        }
    }

    @Override
    public Map<DBPrivsType, Boolean> dbPrivs(DBInfo dbInfo) throws Exception
    {
        Map<DBPrivsType, Boolean> res = new HashMap<>();
        try (Connection conn = getConn(dbInfo);
             Statement sm = conn.createStatement();
             ResultSet currentUser = sm.executeQuery("SELECT CURRENT_USER()");
        )
        {
            if (currentUser.next())
            {
                String[] split = currentUser.getString(1).split("@");
                if ("root".equals(split[0]))
                    return new HashMap<DBPrivsType, Boolean>()
                    {{
                        put(DBPrivsType.DROP, true);
                        put(DBPrivsType.CREATE, true);
                    }};
                try (
                        Statement sm3 = conn.createStatement();
                        ResultSet dbRs = sm3.executeQuery(StrUtil.format("SELECT DROP_PRIV, CREATE_PRIV FROM MYSQL.DB WHERE USER = '{}' AND HOST = '{}' AND DB = '{}'", split[0], split[1], dbInfo.getOwner()));
                )
                {

                    if (dbRs.next())
                    {
                        res.put(DBPrivsType.DROP, "Y".equals(dbRs.getString(1)));
                        res.put(DBPrivsType.CREATE, "Y".equals(dbRs.getString(2)));
                    }
                }
            }
        } catch (Exception e)
        {
            log.error("查询异常", e);
            throw e;
        }
        return res;
    }

    private void getPrivResult(ResultSet rs, Map<DBPrivsType, Boolean> map) throws SQLException
    {
        BiFunction<DBPrivsType, Boolean, Boolean> biFunction = (key, oldValue) ->
        {
            try
            {
                return (oldValue == null || !oldValue) ? "Y".equals(rs.getString(key.ordinal() + 1)) : oldValue;
            } catch (SQLException throwables)
            {
                return false;
            }
        };
        if (rs.next())
        {
            map.compute(DBPrivsType.SELECT, biFunction);
            map.compute(DBPrivsType.INSERT, biFunction);
            map.compute(DBPrivsType.UPDATE, biFunction);
            map.compute(DBPrivsType.DELETE, biFunction);
            map.compute(DBPrivsType.TRUNCATE, biFunction);
        }
    }

    @Override
    public TableInfo tableCount(TableInfo tableInfo) throws Exception
    {
        try (Connection conn = getConn(tableInfo.getDbInfo());
             Statement sm = conn.createStatement();
             ResultSet rs = sm.executeQuery(StrUtil.format("SELECT table_rows, index_length, data_length FROM information_schema.tables where table_schema = '{}' AND table_name = '{}'", tableInfo.getDbInfo().getOwner(), tableInfo.getTableName()));
        )
        {
            if (rs.next())
            {
                tableInfo.setRowSize(rs.getInt(1));
                tableInfo.setIndexSize(rs.getLong(2));
                tableInfo.setDataSize(rs.getLong(3));
                return tableInfo;
            }
        }
        return tableInfo;
    }

    @Override
    public List<FieldInfo> cols(TableInfo tableInfo) throws Exception
    {
        List<FieldInfo> cols = super.cols(tableInfo);
        if (CollUtil.isNotEmpty(cols)) return cols;
        try (Connection conn = getConn(tableInfo.getDbInfo());
             Statement sm = conn.createStatement();
             ResultSet rs = sm.executeQuery(StrUtil.format("SELECT c.column_name, c.is_nullable, c.data_type, c.CHARACTER_MAXIMUM_LENGTH, c.column_type, c.column_key, s.seq_in_index, c.column_comment, c.numeric_precision FROM information_schema.columns c LEFT JOIN information_schema.statistics s ON c.table_schema = s.table_schema AND c.table_name = s.table_name  AND c.column_name = s.column_name WHERE c.table_name = '{}' AND c.table_schema = '{}' ORDER BY c.ordinal_position", tableInfo.getTableName(), tableInfo.getDbInfo().getOwner()));
        )
        {
            cols = new ArrayList<>();
            while (rs.next())
            {
                FieldInfo fieldInfo = new FieldInfo();
                fieldInfo.setTableInfo(tableInfo);
                fieldInfo.setFieldName(rs.getString(1));
                fieldInfo.setNullable("YES".equals(rs.getString(2)));
                fieldInfo.setSourceType(rs.getString(3));
                fieldInfo.setLength(getFieldLength(rs.getString(4), rs.getString(5)));
                fieldInfo.setCommonType(fieldInfo.getLength() > 4000 && convertFieldType(fieldInfo.getSourceType()) == FieldType.VARCHAR ? FieldType.CLOB : convertFieldType(fieldInfo.getSourceType()));
                fieldInfo.setIsPK("PRI".equals(rs.getString(6)));
                String index = rs.getString(7);
                if (index != null)
                    fieldInfo.setPkInd(Integer.parseInt(index));
                fieldInfo.setComment(rs.getString(8));
                fieldInfo.setPrecision(Convert.toInt(rs.getString(9), 0));
                cols.add(fieldInfo);
            }
        } catch (Exception e)
        {
            log.error("查询错误", e);
            throw e;
        }
        return cols;
    }

    @Override
    public DBInfo url2DBInfo(String jdbcUrl)
    {
        if (jdbcUrl.indexOf("?") == -1)
        {
            jdbcUrl += "?";
        }
        List<String> allGroups = ReUtil.getAllGroups(PatternPool.get("jdbc:mysql://(.*?):(.*?)/(.*?)\\?(.*)", 32), jdbcUrl, false);
        ConnInfo connInfo = new ConnInfo();
        connInfo.setIp(allGroups.get(0));
        connInfo.setPort(NumberUtil.parseInt(allGroups.get(1)));
        connInfo.setDbName(allGroups.get(2));
        connInfo.setAttr(allGroups.get(3));
        DBInfo dbInfo = new DBInfo();
        dbInfo.setDbType(DBType.MYSQL);
        dbInfo.setJdbcUrl(jdbcUrl);
        dbInfo.setOwner(connInfo.getDbName());
        dbInfo.setConnInfo(connInfo);
        return dbInfo;
    }

    @Override
    public String dbInfo2Url(DBInfo dbInfo)
    {
        ConnInfo connInfo = dbInfo.getConnInfo();
        if (connInfo == null)
            throw new NoSuchElementException("未找到连接配置信息");
        return StrUtil.isEmpty(connInfo.getAttr()) ? StrUtil.format("jdbc:mysql://{}:{}/{}", connInfo.getIp(), connInfo.getPort(), connInfo.getDbName()) : StrUtil.format("jdbc:mysql://{}:{}/{}?{}", connInfo.getIp(), connInfo.getPort(), connInfo.getDbName(), connInfo.getAttr());
    }

    @Override
    public FieldType convertFieldType(String type) throws NotSupportException
    {
        return super.convertFieldType(TYPEMAP, type);
    }

    @Override
    public String escapeCharacter(String sql)
    {
        return sql.replace("\\", "\\\\").replace("'", "\\'");
    }

    @Override
    public String updateColumnSql(FieldInfo fieldInfo) throws NotSupportException
    {
        String dbName = fieldInfo.getTableInfo().getDbInfo().getOwner();
        String tableName = fieldInfo.getTableInfo().getTableName();
        String colName = fieldInfo.getFieldName();
        String defaults = fieldInfo.getDefaultStr();
        if (!getDEFAULTMAP().containsKey(fieldInfo.getCommonType()))
            throw new NotSupportException("不支持的字段类型:" + fieldInfo.getCommonType().getType());
        return StrUtil.format("ALTER TABLE {}.{} MODIFY COLUMN {} {} {} {}", dbName, tableName, colName, getTypeSql(fieldInfo), StrUtil.isBlank(defaults) ? " " : " DEFAULT " + defaults, StrUtil.isEmpty(fieldInfo.getComment()) ? " " : StrUtil.format("COMMENT '{}'", fieldInfo.getComment()));
    }

    @Override
    public String delColumnSql(FieldInfo fieldInfo) throws NotSupportException
    {
        return StrUtil.format("ALTER TABLE {}.{} DROP {}", fieldInfo.getTableInfo().getDbInfo().getOwner(), fieldInfo.getTableInfo().getTableName(), fieldInfo.getFieldName());
    }

    @Override
    public String funD2C(String fieldName, boolean dateOrTimestamp)
    {
        return dateOrTimestamp ? String.format("to_char(%s, 'yyyy-mm-dd hh24:mi:ss.ff')", fieldName) : String.format("to_char(to_timestamp(%s), 'yyyy-mm-dd hh24:mi:ss.ff')", fieldName);
    }

}
